using System;
using System.Data;
using System.Data.SqlClient;

namespace FAS.DataBase
{
    public class SqlDbController : ASqlDbController
    {
        public SqlDbController()
            : base()
        {
            SetCommandProperty();
        }

        public SqlDbController(bool bIsPoolingOn)
            : base(bIsPoolingOn)
        {
            SetCommandProperty();
        }

        public SqlDbController(string strConnectionString)
            : base(strConnectionString)
        {
            SetCommandProperty();
        }

        public override DataSet Select(string strSQL)
        {
            SqlDataAdapter objDataAdapter = new SqlDataAdapter();
            DataSet objDataSet = new DataSet();
            try
            {
                if (OpenConnection())
                {
                    m_objSqlCommand.CommandText = strSQL;
                    m_objSqlCommand.CommandType = CommandType.Text;
                    objDataAdapter.SelectCommand = m_objSqlCommand;
                    objDataAdapter.Fill(objDataSet);
                }

                CloseConnection();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            return objDataSet;
        }

        public override DataSet Select(string strSQL, SqlParameter[] objParas)
        {
            SqlDataAdapter objDataAdapter = new SqlDataAdapter();
            DataSet objDataSet = new DataSet();
            try
            {
                if (OpenConnection())
                {
                    m_objSqlCommand.Parameters.Clear();
                    foreach (SqlParameter objSqlParameter in objParas)
                    {
                        m_objSqlCommand.Parameters.Add(objSqlParameter);
                    }

                    m_objSqlCommand.CommandText = strSQL;
                    objDataAdapter.SelectCommand = m_objSqlCommand;
                    objDataAdapter.Fill(objDataSet);
                }

                CloseConnection();
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return objDataSet;
        }

        public override int Execute(string strSQL)
        {
            int intResult = -1;
            try
            {
                if (OpenConnection())
                {
                    m_objSqlCommand.CommandText = strSQL;
                    m_objSqlCommand.CommandType = CommandType.Text;

                    intResult = m_objSqlCommand.ExecuteNonQuery();
                }

                CloseConnection();
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return intResult;
        }

        public override int Execute(string strSQL, SqlParameter[] objParas)
        {
            int intResult = -1;
            try
            {
                if (OpenConnection())
                {
                    m_objSqlCommand.Parameters.Clear();
                    foreach (SqlParameter objSqlParameter in objParas)
                    {
                        m_objSqlCommand.Parameters.Add(objSqlParameter);
                    }

                    m_objSqlCommand.CommandText = strSQL;
                    m_objSqlCommand.CommandType = CommandType.Text;

                    intResult = m_objSqlCommand.ExecuteNonQuery();
                }

                CloseConnection();
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return intResult;
        }

        private void SetCommandProperty()
        {
            m_objSqlCommand.CommandType = CommandType.Text;
            m_objSqlCommand.Connection = this.GetConnection();
        }

        public override DataSet SpSelect(string strSqlStatement, SqlParameter[] objParas)
        {
            SqlDataAdapter objDataAdapter = new SqlDataAdapter();
            DataSet objDataSet = new DataSet();
            try
            {
                if (OpenConnection())
                {
                    m_objSqlCommand.Parameters.Clear();
                    foreach (SqlParameter objSqlParameter in objParas)
                    {
                        m_objSqlCommand.Parameters.Add(objSqlParameter);
                    }

                    m_objSqlCommand.CommandType = CommandType.StoredProcedure;
                    m_objSqlCommand.CommandTimeout = 1800;
                    m_objSqlCommand.CommandText = strSqlStatement;
                    objDataAdapter.SelectCommand = m_objSqlCommand;
                    objDataAdapter.Fill(objDataSet);
                }

                CloseConnection();
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return objDataSet;
        }

        public override DataSet SpSelect(string strSqlStatement)
        {
            SqlDataAdapter objDataAdapter = new SqlDataAdapter();
            DataSet objDataSet = new DataSet();
            try
            {
                if (OpenConnection())
                {
                    m_objSqlCommand.Parameters.Clear();
                    m_objSqlCommand.CommandType = CommandType.StoredProcedure;
                    m_objSqlCommand.CommandTimeout = 1800;
                    m_objSqlCommand.CommandText = strSqlStatement;
                    objDataAdapter.SelectCommand = m_objSqlCommand;
                    objDataAdapter.Fill(objDataSet);
                }

                CloseConnection();
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return objDataSet;
        }

        public override void SpExecute(string strSpName, SqlParameter[] objParas)
        {
            try
            {
                if (OpenConnection())
                {
                    m_objSqlCommand.CommandType = CommandType.StoredProcedure;

                    m_objSqlCommand.Parameters.Clear();
                    foreach (SqlParameter objSqlParameter in objParas)
                    {
                        m_objSqlCommand.Parameters.Add(objSqlParameter);
                    }

                    m_objSqlCommand.CommandTimeout = 1800;
                    m_objSqlCommand.CommandText = strSpName;
                    m_objSqlCommand.ExecuteNonQuery();
                }

                CloseConnection();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        public override void SpExecute(string strSpName)
        {
            try
            {
                if (OpenConnection())
                {
                    m_objSqlCommand.CommandType = CommandType.StoredProcedure;

                    m_objSqlCommand.CommandTimeout = 1800;
                    m_objSqlCommand.CommandText = strSpName;
                    m_objSqlCommand.ExecuteNonQuery();
                }

                CloseConnection();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        public override string GetPoolingString(bool bIsPoolingOn)
        {
            string result = "Pooling=" + (bIsPoolingOn ? "True" : "False");

            return result;
        }
    }
}
